/**
 * @filename MemberController.java
 */
package com.liupf.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.aop.Before;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.jfinal.util.PathUtil;
import com.liupf.common.utils.Constants;
import com.liupf.common.utils.MD5Util;
import com.liupf.common.utils.SmsUtil;
import com.liupf.common.utils.TimeUtil;
import com.liupf.common.utils.TypeConstant;
import com.liupf.common.utils.TypeConstant.SmsType;
import com.liupf.domain.Member;
import com.liupf.domain.MembersTel;
import com.liupf.domain.Order;

/**
 * @description 会员管理
 * @author shevliu
 * @email shevliu@gmail.com Jul 28, 2012 3:20:49 PM
 */
public class VipMemberController extends Controller {

	private Log log = LogFactory.getLog(this.getClass());

	public void index() {
		
		//操作员自己所属品牌
		String company=(String) getSession().getAttribute("company");
		int purview=(Integer) getSession().getAttribute("purview");
		String userRole = (String)getSession().getAttribute("userRole");
		
		String realName = getPara("real_name");
		String cellPhone = getPara("cell_phone");
		String type=getPara("type");
		StringBuffer from  = new StringBuffer();
		if("1".equals(type)){
			from.append("from  driver_member m LEFT JOIN driver_members_telephone b ON m.id=b.`memberid` where m.user_level=2 ");
		}
		else{
			from.append("from  driver_member m  where m.user_level=2 ");
		}
		if("0".equals(type) || "1".equals(type)){
		    from.append(" and m.user_type="+type);
		}
		
		if(StringUtils.isNotBlank(realName)){
			from.append(" and m.real_name='" + realName + "'") ;
		}
		if(StringUtils.isNotBlank(cellPhone)){
			from.append(" and m.cell_phone='" + cellPhone + "'") ;
		}
		
		if(Constants.ROLE_BRAND_MANAGER.equals(userRole) || Constants.ROLE_MIDDLE_MANAGER.equals(userRole)){
			from.append(" and m.company like '" + company + "%'");
		}
		
		//会员本身 无品牌
		if("1".equals(type)){
			from.append(" GROUP BY m.id order by m.id desc");
		}
		else{
			from.append(" order by m.id desc");
		}
		String sql="";
		if("1".equals(type)){
			sql="select   m.id,m.real_name,m.`cell_phone`,m.`adds`,m.is_deal,m.`balance`,m.`create_time`,m.user_name,m.`status`,m.user_type,m.`remark`, b.`telephone`,b.`name`, (select count(*) from order_detail d where d.member_id = m.id) order_count, (select sum(d.totalmoney) from order_detail d where d.member_id = m.id) sum_money ";
		}
		else{
			sql="select m.*,(select count(*) from order_detail d where d.member_id = m.id) order_count, (select sum(d.totalmoney) from order_detail d where d.member_id = m.id) sum_money ";
		}
		Page<Member> page = Member.dao.paginate(getParaToInt(0, 1), 10,
				sql,
				from.toString());
		setAttr("page", page);
		if("0".equals(type) || "1".equals(type)){
		    //setUrlPara("?user_type="+type);
		    setAttr("type", type);
		}
		else{
		    setAttr("type", "-1");
		}
		render("vipmember_index.ftl");
	}
	
	public void add(){
		render("vipmember_add.ftl");
	}
	
	public void save(){
		String company=(String) getSession().getAttribute("company");
		
		Member member = getModel(Member.class);
		String password = member.getStr("password");
		member.put("password", MD5Util.MD5(password));
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = formatter.format(new Date());
		member.put("create_time", dateString);
		member.put("user_level", 2);
		member.put("company", company);
		member.put("balance", new BigDecimal("0"));
		member.save();
		redirect("/vipmember");
	}
	
	//保存企业VIP
	public void saveOrgin(){
	    String company=(String) getSession().getAttribute("company");
	    
		String real_name=getPara("real_name");//单位名称
		String user_name=getPara("user_name");//联系人
		String adds=getPara("adds");//地址
		String cell_phone=getPara("cell_phone");//联系电话
		String remark=getPara("remark");//备注
		
		String is_deal=getPara("is_deal");//签单
		if(is_deal==null)
		    is_deal="0";
		else{
		    is_deal="1";
		}
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = formatter.format(new Date());
		
		
		Member members=getModel(Member.class);
		members.put("company", company);
		members.put("create_time", dateString);
		members.put("real_name", real_name);
		members.put("user_name", user_name);
		members.put("adds", adds);
		members.put("remark",remark);
		members.put("is_deal", is_deal);
		members.put("user_type",1);
		members.put("user_level", 2);
		members.put("balance", "0");
		members.put("cell_phone", cell_phone);
		members.save();
		
		//保存手机号
		int memberid=members.getInt("id");
		String tels=getPara("tels");
		com.alibaba.fastjson.JSONArray array=JSONArray.parseArray(tels);
		for (int i=0;i<array.size();i++) {
			JSONObject telephone=array.getJSONObject(i);
			MembersTel tel=getModel(MembersTel.class);
			tel.put("memberid", memberid);
			tel.put("telephone", telephone.getString("telephone"));
			tel.put("name", telephone.getString("name"));
			tel.put("address", telephone.getString("address"));
			tel.put("car_id", telephone.getString("car_id"));
			tel.save();
		}
		
		
		setAttr("type", "1");
		redirect("/vipmember?type=1",true);
		//forwardAction("/vipmember?type=1");
	}
	
	public void edit(){
	    Member member=Member.dao.findById(getParaToInt());
		setAttr("member", member);
		if(member.getInt("user_type")==1){
		    String sql="select * from driver_members_telephone where memberid="+getParaToInt();
		    List<MembersTel> list=MembersTel.dao.find(sql);
		    setAttr("telList", list);
		}
		
		render("vipmember_edit.ftl");
	}
	
	public void todeposit(){
		setAttr("member", Member.dao.findById(getParaToInt()));
		render("vipmember_deposit.ftl");
	}
	
	@Before(Tx.class)
	public void deposit(){
		int userId = (Integer) getSession().getAttribute("userId");
		String company=(String) getSession().getAttribute("company");
		String userName=(String) getSession().getAttribute("userName");
		String remark=getPara("remark");
		
		int memberId = getParaToInt();
		BigDecimal balance = new BigDecimal(getPara("balance"));
		int num = Db.update("update driver_member set balance=ifnull(balance,0)+? where id=? and user_level=2",balance,memberId);
		String type="1";
		if(num == 1){
			Member member = Member.dao.findById(getParaToInt());
			type=member.getInt("user_type").toString();
			Db.update("insert into driver_member_deposit(member_id,deposit_amt,after_balance,create_time,sys_user_id,company,sys_name,user_name,user_phone,deposit_type,remark) values(?,?,?,?,?,?,?,?,?,?,?)", 
					memberId,balance,member.getBigDecimal("balance"),new Date(),userId,company,userName,member.getStr("real_name"),member.getStr("cell_phone"),TypeConstant.DepositType.CASH.name(),remark);
		}
		redirect("/vipmember?type="+type);
	}
	
	public void update(){
		Member member = getModel(Member.class);
		member.remove("password");
		member.update();
		
		Member member1 = Member.dao.findById(member.getInt("id"));
		int userLevel = member1.getInt("user_level");
		if(userLevel == 2){
			redirect("/vipmember");
		}else if(userLevel == 3){
			redirect("/vipmember/applylist");
		}
	}
	
	//更新
	public void updateOrign(){
	    Member member=getModel(Member.class);
	    member.remove("password");
	    
	    String is_deal=getPara("is_deal");//签单
        if(is_deal==null)
            is_deal="0";
        else{
            is_deal="1";
        }
        member.set("is_deal", is_deal);
        member.update();
        
        //手机号
        int memberid=member.getInt("id");	
        String tels=getPara("tels");
        com.alibaba.fastjson.JSONArray array=JSONArray.parseArray(tels);
        String sql="delete from driver_members_telephone where memberid="+memberid;
        Db.update(sql);
        for (int i=0;i<array.size();i++) {
            JSONObject telephone=array.getJSONObject(i);
            MembersTel tel=getModel(MembersTel.class);
            tel.put("memberid", memberid);
            tel.put("telephone", telephone.getString("telephone"));
            tel.put("name", telephone.getString("name"));
            tel.put("address", telephone.getString("address"));
            tel.put("car_id", telephone.getString("car_id"));
            tel.save();
        }
        
        setAttr("type", "1");
        redirect("/vipmember?type=1",true);
	}
	
	//更新机构客户
	public void updaeOrign(){
		
	}
	
	public void delete(){
		int id = getParaToInt(0);
		Member member=Member.dao.findById(id);
		Db.update("update driver_member set status=0 where id=?", id);
		setAttr("type", member.getInt("user_type"));
		redirect("/vipmember?type="+member.getInt("user_type"));
	}
	
	public void black(){
		int id = getParaToInt(0);
		Db.update("update driver_member set status=2 where id=?", id);
		redirect("/vipmember");
	}
	
	public void white(){
		int id = getParaToInt(0);
		Db.update("update driver_member set status=1 where id=?", id);
		redirect("/vipmember");
	}
	
	public void operator() {
		String realName = getPara("real_name");
		String cellPhone = getPara("cell_phone");
		String beginDateStr = getPara("startTime");
		String endDateStr = getPara("endTime");
		int id = getParaToInt("id", 0);
		
		int type=getParaToInt("type", 0);
		

		Date beginDate = TimeUtil.parse(beginDateStr, "yyyy-MM-dd", new Date());
		Date endDate = TimeUtil.parse(endDateStr, "yyyy-MM-dd", new Date());
		beginDate = setTime(beginDate, 0, 0, 0, 0);
		endDate = setTime(endDate, 23, 59, 59, 999);
		
		String company=(String) getSession().getAttribute("company");
		
		String userRole = (String)getSession().getAttribute("userRole");
		StringBuffer from = new StringBuffer();
		from.append(" from driver_member_deposit d where 1=1");
		if(id>0){
		    from.append(" and member_id="+id);
		}
		if (StringUtils	.isNotBlank(realName)) {
			from.append(" and d.user_name='" + realName + "'");
		}
		if (StringUtils.isNotBlank(cellPhone)) {
			from.append(" and d.user_phone='" + cellPhone + "'");
		}
		
		if(Constants.ROLE_BRAND_MANAGER.equals(userRole) || Constants.ROLE_MIDDLE_MANAGER.equals(userRole)){
			from.append(" and d.company like '" + company + "%'");
		}
		
		if (StringUtils.isNotBlank(beginDateStr)) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			from.append(" and d.create_time >='" + sdf.format(beginDate) + "'");
		}
		
		if (StringUtils.isNotBlank(endDateStr)) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			from.append(" and d.create_time <'" + sdf.format(endDate) + "'");
		}
		
		from.append(" order by d.id DESC ");
		Page<Record> page = Db
				.paginate(
						getParaToInt(0, 1),
						10,
						"select *,deposit_amt/100 mat",
						from.toString());
		
		setAttr("page", page);
		// 保存查询条件到页面
		setAttr("real_name", realName);
		setAttr("cell_phone", cellPhone);
		setAttr("startTime", beginDateStr);
		setAttr("endTime", endDateStr);
		setAttr("id", id);

		if(type==0)
			render("deposit_list.ftl");
		else {
			render("depositAll_list.ftl");
		}
	}
	/**
	 * 
	 * @description 根据手机号码查询会员 
	 * @author shevliu
	 * @email shevliu@gmail.com
	 * Jul 28, 2012 11:07:47 PM
	 */
	public void queryMemberByPhone(){
		String phone = getPara("cell_phone");
		Member member = Member.dao.findFirst("select *  ,(select count(*) from order_detail d where d.member_id = m.id) order_count, (select sum(d.charges) from order_detail d where d.member_id = m.id) sum_money from driver_member m where status=1 and cell_phone=?" , phone);
		if(member != null){
			int memberId = member.getInt("id");
			List<Order> orderList = Order.dao.find("select a.* ,c.name as driver_name , c.job_number,c.phone as driver_phone from order_detail a left join b_user c on a.b_user_id = c.id  where member_id=? or cell_phone = ? order by id desc" ,memberId , phone) ;
			setAttr("orderList", orderList);
		}
		setAttr("member", member);
		setAttr("cell_phone", phone);
		render("../order/new_order2.ftl");
	}
	
	public void applylist() {
		
		//操作员自己所属品牌
		String company=(String) getSession().getAttribute("company");
		int purview=(Integer) getSession().getAttribute("purview");
		String userRole = (String)getSession().getAttribute("userRole");
		
		String realName = getPara("real_name");
		String cellPhone = getPara("cell_phone");
		StringBuffer from  = new StringBuffer();
		from.append("from  driver_member m where user_level=3 ");
		if(StringUtils.isNotBlank(realName)){
			from.append(" and m.real_name='" + realName + "'") ;
		}
		if(StringUtils.isNotBlank(cellPhone)){
			from.append(" and m.cell_phone='" + cellPhone + "'") ;
		}
		
		if(Constants.ROLE_BRAND_MANAGER.equals(userRole) || Constants.ROLE_MIDDLE_MANAGER.equals(userRole)){
			from.append(" and m.company like '" + company + "%'");
		}
		
		//会员本身 无品牌
		from.append(" order by id desc");
		Page<Member> page = Member.dao.paginate(getParaToInt(0, 1), 10,
				"select * , (select count(*) from order_detail d where d.member_id = m.id) order_count, (select sum(d.totalmoney) from order_detail d where d.member_id = m.id) sum_money ",
				from.toString());
		setAttr("page", page);
		render("vipmember_apply.ftl");
	}
	
	public void applyAccept(){
		int memberId = getParaToInt();
		PrintWriter pw = null;
		try {
			pw = getResponse().getWriter();
			Member member = Member.dao.findById(memberId);
			int num = Db.update("update driver_member set user_level=2 where user_level=3 and id=?",memberId);
			if(num == 1){
				String memberPhone = member.getStr("cell_phone");
				String content = "首长，您已经成为我集团尊贵VIP客户，今后我们将竭诚为您提供更优质的服务，老兵集团全体员工在此祝：您生活愉快！";
				SmsUtil.sendMessage(memberPhone,content, SmsType.LBJT.name());
				
				//String insertMessage = "insert into tel_company_message(TEL,MESSAGE,COMPANY,CREATE_TIME) values(?,?,?,?)";
				//Db.update(insertMessage,memberPhone,content,TypeConstant.admin,new java.sql.Date(Calendar.getInstance().getTime().getTime()));
				
				pw.print("1");
			}else{
				pw.print("0");
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally{
			if(pw != null){
				pw.close();
				pw = null;
			}
		}
	}
	
	public void exportXls() {
		int type=getParaToInt();
		if(type==1){
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet();
			HSSFRow row = sheet.createRow((short) 0);
			row.createCell(0).setCellValue("企业名称");
			row.createCell(1).setCellValue("联系人");
			row.createCell(2).setCellValue("联系电话");
			row.createCell(3).setCellValue("单位地址");
			row.createCell(4).setCellValue("使用人");
			row.createCell(5).setCellValue("使用电话");
			row.createCell(6).setCellValue("是否签单");
			row.createCell(7).setCellValue("订单量");
			row.createCell(8).setCellValue("订单额");
			
			row.createCell(9).setCellValue("注册时间");
			row.createCell(10).setCellValue("状态");
			row.createCell(11).setCellValue("备注");
	
			
			List<Record> memberList = Db.find("select m.*,b.* , (select count(*) from order_detail d where d.member_id = m.id) order_count, (select sum(d.totalmoney) from order_detail d where d.member_id = m.id) sum_money "
					                          +"from  driver_member m LEFT JOIN driver_members_telephone b ON m.id=b.`memberid` WHERE m.user_type=1 GROUP BY m.id  order by m.id desc");
			
			for (int i = 0; i < memberList.size(); i++) {
				HSSFRow r = sheet.createRow(i + 1);
				Record record = memberList.get(i);
				
				r.createCell(0).setCellValue(record.getStr("user_name"));
				r.createCell(1).setCellValue(record.getStr("real_name"));
				r.createCell(2).setCellValue(record.getStr("cell_phone"));
				r.createCell(3).setCellValue(record.getStr("adds"));
				r.createCell(4).setCellValue(record.getStr("name") );
				r.createCell(5).setCellValue(record.getStr("telephone"));
				
				int deal = record.getInt("is_deal");
				if(deal == 0) {
					r.createCell(6).setCellValue("否");
				} else if(deal ==1) {
					r.createCell(6).setCellValue("是");
				} else {
					r.createCell(6).setCellValue("否");
				}
				r.createCell(7).setCellValue(record.getLong("order_count"));
				r.createCell(8).setCellValue(record.getLong("sum_money")!= null ? record.getLong("sum_money").doubleValue():0);
				r.createCell(9).setCellValue(record.getTimestamp("create_time").toString());
				int status = record.getInt("status");
				if(deal == 1) {
					r.createCell(10).setCellValue("正常");
				} else if(deal ==2) {
					r.createCell(10).setCellValue("黑名单");
				} else {
					r.createCell(10).setCellValue("已删除");
				}
				r.createCell(11).setCellValue(record.getStr("remark"));
			}
			PathUtil.getWebRootPath();
			String fileName = PathUtil.getWebRootPath() + "/excel/vipmember.xls";
			File f = new File(fileName);
			log.debug("file path:" + f.getAbsolutePath());
			FileOutputStream fOut;
			try {
				fOut = new FileOutputStream(fileName);
				workbook.write(fOut);
				fOut.flush();
				fOut.close();
			} catch (Exception e) {
				throw new RuntimeException("导出excel失败，请稍后再试", e);
			}
			renderFile(f);
		}
		else{
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet();
			HSSFRow row = sheet.createRow((short) 0);
			row.createCell(0).setCellValue("姓名");
			row.createCell(1).setCellValue("手机号码");
			row.createCell(2).setCellValue("性别");
			row.createCell(3).setCellValue("车牌号码");
			row.createCell(4).setCellValue("是否签单");
			row.createCell(5).setCellValue("订单量");
			row.createCell(6).setCellValue("订单总额");
			row.createCell(7).setCellValue("账户余额");
			row.createCell(8).setCellValue("身份证");
			
			row.createCell(9).setCellValue("邮件");
			row.createCell(10).setCellValue("常用地址");
			row.createCell(11).setCellValue("注册时间");
			row.createCell(12).setCellValue("状态");
			row.createCell(13).setCellValue("备注");
			
			
			List<Record> memberList = Db.find("select m.* , (select count(*) from order_detail d where d.member_id = m.id) order_count, (select sum(d.totalmoney) from order_detail d where d.member_id = m.id) sum_money "
					                          +"from  driver_member m WHERE m.user_type=0 and m.user_level=2  order by m.id desc");
			
			for (int i = 0; i < memberList.size(); i++) {
				HSSFRow r = sheet.createRow(i + 1);
				Record record = memberList.get(i);
				
				r.createCell(0).setCellValue(record.getStr("real_name"));
				r.createCell(1).setCellValue(record.getStr("cell_phone"));
				int sex = record.getInt("sex");
				if(sex == 0) {
					r.createCell(2).setCellValue("男");
				} else if(sex ==1) {
					r.createCell(2).setCellValue("女");
				} else {
					r.createCell(2).setCellValue("未知");
				}
				r.createCell(3).setCellValue(record.getStr("plage_number"));
				int deal=0;
				if( record.getInt("is_deal")==null)
					deal=0;
				else
					deal = record.getInt("is_deal");
				if(deal == 0) {
					r.createCell(4).setCellValue("否");
				} else if(deal ==1) {
					r.createCell(4).setCellValue("是");
				} else {
					r.createCell(4).setCellValue("否");
				}
				r.createCell(5).setCellValue(record.getLong("order_count"));
				r.createCell(6).setCellValue(record.getBigDecimal("sum_money")!= null ? record.getBigDecimal("sum_money").doubleValue():0);
				
				r.createCell(7).setCellValue(record.getBigDecimal("balance").doubleValue());
				r.createCell(8).setCellValue(record.getStr("card_id"));
				r.createCell(9).setCellValue(record.getStr("email_no"));
				r.createCell(10).setCellValue(record.getStr("endpoint"));
				r.createCell(11).setCellValue(record.getTimestamp("create_time").toString());
				int status = record.getInt("status");
				if(status == 1) {
					r.createCell(12).setCellValue("正常");
				} else if(status ==2) {
					r.createCell(12).setCellValue("黑名单");
				} else {
					r.createCell(12).setCellValue("已删除");
				}
				r.createCell(13).setCellValue(record.getStr("remark"));
			}
			PathUtil.getWebRootPath();
			String fileName = PathUtil.getWebRootPath() + "/excel/vipmember.xls";
			File f = new File(fileName);
			log.debug("file path:" + f.getAbsolutePath());
			FileOutputStream fOut;
			try {
				fOut = new FileOutputStream(fileName);
				workbook.write(fOut);
				fOut.flush();
				fOut.close();
			} catch (Exception e) {
				throw new RuntimeException("导出excel失败，请稍后再试", e);
			}
			renderFile(f);
		}
	
	}
	
	private Date setTime(Date date, int hour, int minute, int second, int ms) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		calendar.set(Calendar.MINUTE, minute);
		calendar.set(Calendar.SECOND, second);
		calendar.set(Calendar.HOUR_OF_DAY, hour);
		calendar.set(Calendar.MILLISECOND, ms);
		date = calendar.getTime();
		return date;
	}
	
	//充值记录
	/*private void depositHistory(){
	    int id = getParaToInt(0);
	    StringBuffer from=new StringBuffer();
	    from.append(" from driver_member_deposit where member_id="+id);
	    Page<MemberDeposit> page = MemberDeposit.dao.paginate(getParaToInt(0, 1), 10,
            "select * ",
            from.toString());
	    setAttr("page", page);
	    reder("");
	}*/

}
